//引入依赖文件
var express = require('express')
var app = express();
var db = require('./conn')
app.use((req, res, next) => {
    // 设置是否运行客户端设置 withCredentials
    // 即在不同域名下发出的请求也可以携带 cookie
    res.header("Access-Control-Allow-Credentials", true)
    // 第二个参数表示允许跨域的域名，* 代表所有域名  
    res.header('Access-Control-Allow-Origin', req.headers.origin)
    res.header('Access-Control-Allow-Methods', 'GET, PUT, POST, DELETE, OPTIONS') // 允许的 http 请求的方法
    // // 允许前台获得的除 Cache-Control、Content-Language、Content-Type、Expires、Last-Modified、Pragma 这几张基本响应头之外的响应头
    res.header('Access-Control-Allow-Headers', 'Content-Type, Authorization, Content-Length, X-Requested-With')
    // if (req.method == 'OPTIONS') {
    //     res.sendStatus(200)
    // } else {
    //     next()
    // }
    next()
})
//允许使用服务器端静态路径,app.use是调用中间件
// 解析 application/json
app.use(express.json())
// 解析 application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: false }));
app.use(express.static('./'))
//项目接口合集-------------------------------


//1.注册
app.post('/competition/user/reguser', (req, res) => {
    var addSql = 'select * from user where username=? '
    db.query(addSql, [req.body.username], (list, results, field) => {
        if (list) {
            /*判断数据库返回服务端的数据是否是空列表,
        如果是空列表 :则数据库没查到该用户名;否则就是查到了*/
            if (list.length != 0) {
                res.send({
                    success: false,
                    msg: '用户已存在，请重新注册！'
                })
            } else {
                console.log(list);
                //向数据库添加username和psw user_img
                var sql = 'insert into user(username,password) values(?,?)'
                db.query(sql, [req.body.username, req.body.password], (list, field) => {
                    if (list) {
                        res.send({ success: true, msg: '注册成功！' })
                    } else {
                        console.log('db' + list);
                        res.send({
                            success: true, msg: '注册失败,sql语句有误！'
                        })
                    }
                })
            }
        }

    })

})

//2.登录
app.post('/competition/user/login', (req, res) => {
    var sql = 'select * from user where username=? and password = ?'
    db.query(sql, [req.body.username, req.body.password], (list, field) => {
        if (list) {
            if (list.length != 0) {
                // console.log(list.length);
                res.send({
                    success: true,
                    msg: '登录成功！',
                    data: list[0]
                })
            } else {
                res.send({
                    success: false,
                    msg: '账号或密码错误,请重新输入'
                })
            }
        }
    })
})

// 修改密码
app.post('/competition/user/update/password', (req, res) => {

    var sql = 'select * from user where username=? and password = ?'
    db.query(sql, [req.body.username, req.body.password], (list, field) => {
        if (list) {
            if (list.length != 0) {
                console.log(list.length);
                var updatesql = 'UPDATE user SET password = ? WHERE username = ?';
                db.query(updatesql, [req.body.password, req.body.username], (error, results, fields) => {
                    if (error) {
                        // 处理数据库错误  
                        return res.status(500).send({ success: false, msg: '数据库错误' });
                    }
                    if (results.affectedRows > 0) {
                        // 如果受影响的行数大于 0，表示密码更新成功  
                        res.send({
                            success: true,
                            msg: '密码更新成功'
                        });
                    } else {
                        // 如果没有行被更新，可能是用户名或旧密码错误  
                        res.send({
                            success: false,
                            msg: '用户名或旧密码错误'
                        });
                    }
                });
            } else {
                res.send({
                    success: false,
                    msg: '账号或密码错误,请重新输入'
                })
            }
        }
    })

});

//3.查询人员信息表staff
app.post('/competition/staff/list', (req, res) => {
    var sql = 'select * from staff'
    db.query(sql, [], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//4.查询团队信息表 team
app.post('/competition/team/list', (req, res) => {
    var sql = 'select * from team'
    db.query(sql, [], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//5.查询单练表project
app.post('/competition/project/list', (req, res) => {
    var sql = 'select * from project'
    db.query(sql, [], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//6.新增 人员：-----修改过
app.post('/competition/staff/add', (req, res) => {
    // var sql = 'insert into staff(statusname,job,idcard,username,sex,birthday,phone,email,vipdata,danxz,iddan,service,danbk,association,jiazhu,tour,souvenir,censusr,img,csgroup,dkgroup) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);'

    var sql = 'insert into staff(statusname,csgroup,username,idcard,sex, birthday,phone,email) values(?,?,?,?,?,?,?,?);'
    db.query(sql, [
        req.body.statusname,
        req.body.csgroup,
        req.body.username,
        req.body.idcard,
        req.body.sex,
        req.body.birthday,
        req.body.phone,
        req.body.email
    ], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '新增人员成功！'
            })
        } else {
            console.log('db' + list);
            res.send({
                success: false,
                msg: '新增人员失败！'
            })
        }
    })
})
// 6.-new:


//7.新增 团队信息：
app.post('/competition/team/add', (req, res) => {
    var sql = 'insert into team(teamname,teamabbreviation, provinciallevel, citylevel, address,teamleader,phone, email,userid) values(?,?,?,?,?,?,?,?,?);'

    // var sql = 'insert into team(teamname,teamabbreviation,englishname,teamaward,statelevel,nation,provinciallevel,citylevel,address,teamleader,phone,email,vxid,slogan,img) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);'
    db.query(sql, 
        [req.body.teamname, 
            req.body.teamabbreviation, 
            req.body.provinciallevel, 
            req.body.citylevel, 
            req.body.address, 
            req.body.teamleader, 
            req.body.phone,
             req.body.email,
            req.body.userid],
         (list, field) => {
        if (list) {
            console.log(list)
            res.send({
                success: true,
                msg: '新增团队成功！',
                data:{
                    id: list.insertId
                }

            })
        } else {
            console.log('db' + list);
            res.send({
                success: false,
                msg: '新增团队失败！'
            })
        }
    })
})

//8.新增单练人员：：-----修改过
app.post('/competition/project/add', (req, res) => {
    // var sql = 'insert into project(projectid,projectname,routinename,participant,csgroup) values(?,?,?,?,?);'
    var sql = 'insert into project(projectname,routinename,participant) values(?,?,?);'

    db.query(sql, [req.body.projectname, req.body.routinename, req.body.participant], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '新增单练人员成功！',
                data:{
                    id:list.insertId
                }
            })
        } else {
            console.log('db' + list);
            res.send({
                success: false,
                msg: '新增单练人员失败！'
            })
        }
    })
})

//9.查询首页表 home: res.data.list
app.post('/competition/home/list', (req, res) => {
    var sql = 'select * from home'
    db.query(sql, [], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

// 10. 分页
app.post('/competition/staff/paging/list', (req, res) => {
    var sql = 'SELECT * FROM staff LIMIT ? OFFSET ?'
    // handleRequest(req, res)
    db.query(sql, [req.body.page, req.body.pageSize], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//11.查询所有赛事:
app.post('/competition/classification/list', (req, res) => {
    var sql = 'select * from classification'
    db.query(sql, [], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//12.通过id查询赛事：
app.post('/competition/classification/getbyId', (req, res) => {
    var sql = 'select * from classification where id = ?'
    db.query(sql, [req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//13.通过姓名查询人员信息：staff
app.post('/competition/staff/getByusername', (req, res) => {
    var sql = 'select * from staff where username = ?'
    db.query(sql, [req.body.username], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//14. 下一页staff
app.post('/staff/limit', (req, res) => {
    handleRequest(req, res)
})
function handleRequest(req, res) {
    // 查询总条数：count
    db.query('select count(*) as count from staff', function (err, results2) {
        if (err) throw err;

        const page = parseInt(req.body.page) || 1; //默认页码为1
        const limit = parseFloat(req.body.limit) || 9; //默认每页9条数据
        const offset = (page - 1) * limit; // 计算偏移量 第几页
        // 条件查询：
        db.query('select * from staff limit ? OFFSET ?', [limit, offset], (list, field) => {
            if (list) {
                res.send({
                    success: true,
                    msg: '成功',
                    list
                })
            } else {
                res.send({
                    success: false,
                    msg: '失败'
                })
            }
        })
    });

}

//15. 省级列表：province
app.post('/competition/province/list', (req, res) => {
    var sql = 'select * from province'
    db.query(sql, [], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})
// 16. 市区：
app.post('/competition/city/getByid', (req, res) => {
    var sql = 'select * from city where provinceid = ?'
    db.query(sql, [req.body.provinceid], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

// 17. 修改人员信息
app.post('/competition/staff/update', (req, res) => {
    var sql = 'update staff set statusname=?,job=?,idcard=?,username=?,sex=?,birthday=?,phone=?,email=?,vipdata=?,danxz=?,iddan=?,service=?,danbk=?,association=?,jiazhu=?,tour=?,souvenir=?,censusr=?,img=? where id=?'
    db.query(sql, [req.body.statusname, req.body.job, req.body.idcard, req.body.username, req.body.sex, req.body.birthday, req.body.phone, req.body.email, req.body.vipdata, req.body.danxz, req.body.iddan, req.body.service, req.body.danbk, req.body.association, req.body.jiazhu, req.body.tour, req.body.souvenir, req.body.censusr, req.body.img, req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功'
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

// 18. 删除人员信息
app.post('/competition/staff/del', (req, res) => {
    var sql = 'delete from staff where id = ?'
    db.query(sql, [req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功'
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//19.通过id查询人员信息：
app.post('/competition/staff/getbyId', (req, res) => {
    var sql = 'select * from staff where id = ?'
    db.query(sql, [req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

//20. 下一页：project
app.post('/project/limit', (req, res) => {
    handleRequestproject(req, res)
})
function handleRequestproject(req, res) {
    // 查询总条数：count
    db.query('select count(*) as count from project', function (err, results2) {
        if (err) throw err;

        const page = parseInt(req.body.page) || 1; //默认页码为1
        const limit = parseFloat(req.body.limit) || 9; //默认每页11条数据
        const offset = (page - 1) * limit; // 计算偏移量 第几页
        // 条件查询：
        db.query('select * from project limit ? OFFSET ?', [limit, offset], (list, field) => {
            if (list) {
                res.send({
                    success: true,
                    msg: '成功',
                    list
                })
            } else {
                res.send({
                    success: false,
                    msg: '失败'
                })
            }
        })
    });

}

// 21. 修改:project
app.post('/competition/project/update', (req, res) => {
    var sql = 'update project set projectid=?,projectname=?,routinename=?,participant=? where id=?'
    db.query(sql, [req.body.projectid, req.body.projectname, req.body.routinename, req.body.participant, req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功'
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

// 22. 删除:project
app.post('/competition/project/del', (req, res) => {
    var sql = 'delete from project where id = ?'
    db.query(sql, [req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功'
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})

// 23.通过id：project
app.post('/competition/project/getbyId', (req, res) => {
    var sql = 'select * from project where id = ?'
    db.query(sql, [req.body.id], (list, field) => {
        if (list) {
            res.send({
                success: true,
                msg: '成功',
                list
            })
        } else {
            res.send({
                success: false,
                msg: '失败'
            })
        }
    })
})





app.listen(3002, () => {
    // 3000是监听的端口号,JSP中经常使用8080端口，默认是80端口
    console.log('服务器启动！http://localhost:3002')
})

